package com.xm.util;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Administrator
 * @ClassName BaseDao
 * @Description 连接数据库辅助类
 * @date 2020/6/5 11:03
 */
public class BaseDao {
    public static final String URL = "jdbc:mysql:///commodity?characterEncoding=utf-8";
    public static final String USER = "root";
    public static final String PWD = "123123";
    public static final String DRIVER = "com.mysql.jdbc.Driver";

    /**
     * 获取连接
     * @return
     */
    public static Connection getConnection(){
        Connection conn = null;
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL,USER,PWD);
        }catch (Exception e){
            e.printStackTrace();
        }
        return conn;
    }




    /**
     * 关闭资源
     * @param rs
     * @param ps
     * @param conn
     */
    public static void close(ResultSet rs, PreparedStatement ps,Connection conn){
        try {
            if ( rs != null){
                rs.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        try {
            if ( ps != null){
                ps.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        try {
            if ( conn!= null){
                conn.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }





    /**
     * 公共查询
     * @param cls
     * @return
     */
    public static List<?> queryAll(Class cls){
        List<Object> list = new ArrayList<>();
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String tableName = cls.getSimpleName();
        String sql = "select * from `"+tableName+"`";
        Field [] fields = cls.getDeclaredFields();
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Object bean = cls.newInstance();
                for (int i = 0;i<fields.length;i++){
                    fields[i].setAccessible(true);
                    fields[i].set(bean,rs.getObject(fields[i].getName()));
                }
                list.add(bean);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(rs,ps,conn);
        }
        return list;
    }




    /**
     * 按id查询一个
     * @param cls
     * @param id
     * @return
     */
    public static Object queryOne(Class cls,Object id){
        Object bean = null;
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String tableName = cls.getSimpleName();
        Field [] fields = cls.getDeclaredFields();
        String sql = "select * from `"+tableName+"` where ";
        sql += fields[0].getName()+" = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                bean = cls.newInstance();
                for (int i = 0;i<fields.length;i++){
                    fields[i].setAccessible(true);
                    fields[i].set(bean,rs.getObject(fields[i].getName()));
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(rs,ps,conn);
        }
        return bean;
    }




    /**
     * 按id删除
     * @param cls
     * @param id
     * @return
     */
    public static boolean deletelById(Class cls,Object id){
        boolean result = false;
        Connection conn = getConnection();
        PreparedStatement ps = null;
        String tableName = cls.getSimpleName();
        Field [] fields = cls.getDeclaredFields();
        String sql = "delete from `"+tableName+"` where ";
        sql += fields[0].getName() +"=?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            result = ps.executeUpdate() >0;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(null,ps,conn);
        }
        return result;
    }




    /**
     * 带标识列的公共新增
     * @param object
     * @return
     */
    public static boolean add(Object object){
        Class cls = object.getClass();
        boolean result = false;
        Connection conn = getConnection();
        PreparedStatement ps = null;
        Field [] fields = cls.getDeclaredFields();
        String tableName = cls.getSimpleName();
        String sql = "insert into `"+tableName+"` values(null,";
        for (int i = 1;i<fields.length;i++){
            if (i != fields.length - 1){
                sql += "?,";
            } else {
                sql += "?)";
            }
        }
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 1;i<fields.length;i++){
                fields[i].setAccessible(true);
                ps.setObject(i,fields[i].get(object));
            }
            result = ps.executeUpdate() > 0;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(null,ps,conn);
        }
        return result;
    }



    /**
     * 无标识列公共新增
     * @param object
     * @return
     */
    public static boolean addNotAuto(Object object){
        boolean result = false;
        Class cls = object.getClass();
        Connection conn = getConnection();
        PreparedStatement ps = null;
        String tableName = cls.getSimpleName();
        String sql = "insert into `"+tableName+"` values(";
        Field [] fields = cls.getDeclaredFields();
        try {
            for (int i = 0;i<fields.length;i++){
                if (i != fields.length - 1){
                    sql += "?,";
                } else {
                    sql += "?)";
                }
            }
            ps=conn.prepareStatement(sql);
            for (int i = 0;i<fields.length;i++){
                fields[i].setAccessible(true);
                ps.setObject(i+1,fields[i].get(object));
            }
            result = ps.executeUpdate() > 0;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(null,ps,conn);
        }
        return result;
    }





    /**
     * 查所有并进行分页
     * @param cls 要查询的类
     * @param star 开始页码
     * @param num 每页展示信息条数
     * @return
     */
    public static List<?> queryLimit(Class cls,Integer star,Integer num){
        List<Object> list  = new ArrayList<>();
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String tableName = cls.getSimpleName();
        Field [] fields = cls.getDeclaredFields();
        Integer start = star * num - num;
        String sql = "select * from `"+tableName+"` limit "+start+","+num;
        try {
             ps = conn.prepareStatement(sql);
             rs = ps.executeQuery();
             while (rs.next()){
                 Object bean = cls.getDeclaredConstructor().newInstance();
                 for (int i = 0;i<fields.length;i++){
                     fields[i].setAccessible(true);
                     fields[i].set(bean,rs.getObject(fields[i].getName()));
                 }
                 list.add(bean);
             }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(rs,ps,conn);
        }
        return list;
    }



    /**
     *公共修改
     * @param object
     * @return
     */
    public static boolean update(Object object){
        boolean result = false;
        Connection conn = getConnection();
        PreparedStatement ps = null;
        try {
            Class cls = object.getClass();
            String tableName = cls.getSimpleName();
            String sql = "update `"+tableName+"` set ";
            Field [] fields = cls.getDeclaredFields();
            for (int i = 1;i<fields.length;i++){
                fields[i].setAccessible(true);
                //取出传进来的类的值，用于判断是否对该属性（字段）进行过修改
                Object var = fields[i].get(object);
                //如果该属性不为空，证明进行了修改，就将该属性名拼接进sql语句中
                if (null != var){
                    sql += fields[i].getName() + " = ?,";
                }
            }

            //最后一个属性后面的逗号要去除，否则会报错
            sql = sql.substring(0,sql.length() - 1);
            //默认使用第一个属性作为修改条件
            sql += " where "+fields[0].getName()+" = ?";
            ps = conn.prepareStatement(sql);
            int j = 1;
            for (int i = 1;i<fields.length;i++){
                fields[i].setAccessible(true);
                Object var = fields[i].get(object);
                if (var != null){
                    ps.setObject(j,var);
                    j++;
                }
            }
            fields[0].setAccessible(true);
            ps.setObject(j,fields[0].get(object));
            result = ps.executeUpdate() > 0;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(null,ps,conn);
        }
        return result;
    }



    /**
     * 按条件查询
     * @param object
     * @return
     */
    public static List<?> queryByCondition(Object object){
        List<Object> list = new ArrayList<>();
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Class cls = object.getClass();
        String tableName = cls.getSimpleName();
        String sql = "select * from `"+tableName +"` where ";
        Field [] fields = cls.getDeclaredFields();
        try {
            for (Field field : fields){
                field.setAccessible(true);
                Object var = field.get(object);
                if(var != null){
                    sql += field.getName()+" = ? and ";
                }
            }
            sql = sql.substring(0,sql.length() - 4);
            ps = conn.prepareStatement(sql);
            int j = 1;
            for (Field field : fields){
                field.setAccessible(true);
                Object var = field.get(object);
                if(var != null){
                    ps.setObject(j,var);
                    j++;
                }
            }
            rs = ps.executeQuery();
            while (rs.next()){
                Object bean = cls.newInstance();
                for (int i = 0;i<fields.length;i++){
                    fields[i].setAccessible(true);
                    fields[i].set(bean,rs.getObject(fields[i].getName()));
                }
                list.add(bean);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(rs,ps,conn);
        }
        return list;
    }


    /**
     * 按条件模糊查询
     * @param object
     * @return
     */
    public static List<?> queryByConditionLike(Object object){
        List<Object> list = new ArrayList<>();
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Class cls = object.getClass();
        String tableName = cls.getSimpleName();
        String sql = "select * from `"+tableName +"` where ";
        Field [] fields = cls.getDeclaredFields();
        try {
            for (Field field : fields){
                field.setAccessible(true);
                Object var = field.get(object);
                if(var != null){
                    sql += field.getName()+" like \'%"+field.get(object)+"%\' and ";
                }
            }
            sql = sql.substring(0,sql.length() - 4);
            System.out.println(sql);
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Object bean = cls.newInstance();
                for (int i = 0;i<fields.length;i++){
                    fields[i].setAccessible(true);
                    fields[i].set(bean,rs.getObject(fields[i].getName()));
                }
                list.add(bean);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(rs,ps,conn);
        }
        return list;
    }


    /**
     * 按条件删除
     * @param object
     * @return
     */
    public static boolean deleteByCondition(Object object){
        int count = 0;
        Connection conn = getConnection();
        PreparedStatement ps = null;
        Class cls = object.getClass();
        String tableName = cls.getSimpleName();
        String sql = "delete from `"+tableName+"` where ";
        Field [] fields = cls.getDeclaredFields();
        try {
            for (Field field : fields){
                field.setAccessible(true);
                Object var = field.get(object);
                if(var != null){
                    sql += field.getName()+" = ? and ";
                }
            }
            sql = sql.substring(0,sql.length() - 4);
            ps = conn.prepareStatement(sql);
            int i = 1;
            for(Field field : fields){
                field.setAccessible(true);
                Object var = field.get(object);
                if (var != null){
                    ps.setObject(i,var);
                    i++;
                }
            }
            count = ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(null,ps,conn);
        }
        return count > 0;
    }


    /**
     * 公共修改（按条件）
     * @param object 更改后信息
     * @param oldObject 更改前，作为条件
     * @return
     */
    public static boolean updateByCondity(Object object,Object oldObject){
        int count = 0;
        Connection conn = getConnection();
        PreparedStatement ps = null;
        Class cls = object.getClass();
        Class oldCls = oldObject.getClass();
        String tableName = cls.getSimpleName();
        String sql = "update `"+tableName+"` set ";
        Field [] fields = cls.getDeclaredFields();
        Field [] old = oldCls.getDeclaredFields();
        try {
            for (int i = 1;i<fields.length;i++){
                fields[i].setAccessible(true);
                Object var = fields[i].get(object);
                if(var != null){
                    sql += fields[i].getName()+" = ?,";
                }
            }
            sql = sql.substring(0,sql.length() - 1);
            sql += " where ";
            for (int i = 0;i<old.length;i++){
                old[i].setAccessible(true);
                Object var = old[i].get(oldObject);
                if (var != null){
                    sql += old[i].getName()+" = ? and ";
                }
            }
            sql = sql.substring(0,sql.length() - 4);
            ps = conn.prepareStatement(sql);
            int j = 1;
            for (int i = 1;i<fields.length;i++){
                fields[i].setAccessible(true);
                Object var = fields[i].get(object);
                if (var != null){
                    ps.setObject(j,var);
                    j++;
                }
            }
            for (int i = 0;i<old.length;i++){
                old[i].setAccessible(true);
                Object var = old[i].get(oldObject);
                if (var != null){
                    ps.setObject(j,var);
                    j++;
                }
            }
            count = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(null,ps,conn);
        }
        return count > 0;
    }




    /**
     *只对非空字段进行赋值的新增
     * 例如标识列不能赋值，时间列数据库设置了默认键，不需要赋值的情况
     * @param object
     * @return
     */
    public static boolean addNotAll(Object object){
        int count = 0;
        Connection conn = getConnection();
        PreparedStatement ps = null;
        Class cls = object.getClass();
        String tableName = cls.getSimpleName();
        Field [] fields = cls.getDeclaredFields();
        String sql = "insert into `"+tableName+"`(";
        try {
            int j = 1;
            for (int i = 0;i<fields.length;i++){
                fields[i].setAccessible(true);
                Object var = fields[i].get(object);
                if (var != null){
                    sql += fields[i].getName()+",";
                    j++;
                }
            }
            sql = sql.substring(0,sql.length() - 1)+") values(";
            for (int i = 1;i<j;i++){
                sql += "?,";
            }
            sql = sql.substring(0,sql.length()-1)+")";
            ps = conn.prepareStatement(sql);
            int k = 1;
            for (int i = 0;i<fields.length;i++){
                fields[i].setAccessible(true);
                Object var = fields[i].get(object);
                if (var != null){
                    ps.setObject(k,var);
                    k++;
                }
            }
            count = ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(null,ps,conn);
        }
        return count > 0;
    }



    /**
     *根据名称查询一条数据（多用于傻逼登录）
     * 固定名称是第二个属性
     * @param name
     * @return
     */
    public static Object queryOneByName(Class cls,String name){
        Object object = null;
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String tableName = cls.getSimpleName();
        Field [] fields = cls.getDeclaredFields();
        String sql = "select * from `"+tableName+"` where "+fields[1].getName()+"=?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1,name);
            rs = ps.executeQuery();
            if (rs.next()){
                object = cls.getDeclaredConstructor().newInstance();
                for (Field field : fields){
                    field.setAccessible(true);
                    field.set(object,rs.getObject(field.getName()));
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(rs,ps,conn);
        }
        return object;
    }


    /**
     * 获取表数据条数
     * @param cls
     * @return
     */
    public static Integer queryCount(Class cls){
        int count = 0;
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String tableName = cls.getSimpleName();
        String sql = "select count(*) from `"+tableName+"`";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                count = rs.getInt("count(*)");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(null,ps,conn);
        }
        return count;
    }
	
	/**
     * 统计单表记录数
     * @param cls
     * @return
     */
    public static Integer count(Class<?> cls){
        int count = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        //获取实体类名
        String tableName = cls.getSimpleName();
        String commSql = "select count(1) from `" + tableName+"`";
        conn = getConnection();

        System.out.println("执行单表查询语句："+commSql);
        System.out.println("-----------------------------------------------------------------");
        try {
            ps = conn.prepareStatement(commSql);
            rs = ps.executeQuery();
            if(rs.next()){
               count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.close(rs,ps,conn);
        }
        return count;
    }
}
